import pymysql
import numpy as np
import json
import sqlite3
import os
import face_api


def load_data_from_mysql():
    db = pymysql.connect('localhost', 'root', '123456', 'smarter', charset='utf8')
    cursor = db.cursor()
    cursor.execute("select user_id,name,feature_code,employee_no from sys_user")
    data = cursor.fetchall()
    peoples = []
    for row in data:
        peoples.append((json.loads(row[2])['fea']))
    peoples = np.array(peoples)
    return data, peoples


def store_feature_to_sqlite():
    conn = sqlite3.connect('test.db')
    c = conn.cursor()
    path = r'static/img/pic0/'
    for filename in os.listdir(path):
        print(filename)
        box,fea=face_api.get_feature_face_from_file(os.path.join(path,filename))
        feature_str = json.dumps(fea.tolist())
        user_id = int(filename.split('.')[0])
        c.execute('update sys_user set feature=? where Id=?', (feature_str, user_id))
    conn.commit()


def load_data_from_sqlite():
    conn = sqlite3.connect('test.db')
    c = conn.cursor()
    sql='select Id,name,feature from sys_user'
    data=c.execute(sql).fetchall()
    peoples=[]
    user_dict={}
    for index,row in enumerate(data):
        user_dict[str(row[0])]=str(row[1])
        peoples.append(json.loads(row[2]))
    peoples=np.array(peoples)
    return data,user_dict,peoples


def get_device_addr(device_id):
    sql="select addr from sys_device where Id=?"
    conn=sqlite3.connect('test.db')
    c=conn.cursor()
    cursor=c.execute(sql,device_id)
    result=cursor.fetchone()[0]
    return result



if __name__=='__main__':
    '''
    在这里测试。
    '''
    store_feature_to_sqlite()
    print()


